package id.co.shuba.spamobile.data;

import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import id.co.shuba.spamobile.model.ProspectProgressInfo;
import id.co.shuba.spamobile.model.StandardInfo;
import id.co.shuba.spamobile.model.ProspectInfo;
import id.co.shuba.spamobile.util.Str;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class ProspectDB extends BaseDB {
	public static final String PROSPECT_STATUS_NEW = "NEW";
	public static final String PROSPECT_STATUS_CLOSE_DEAL = "CD";
	public static final String PROSPECT_STATUS_FOLLOW_UP = "FLU";	
	
	public ProspectDB(DatabaseHandler db) {
		super(db);
	}	
	
	public Cursor getListAll() {
		SQLiteDatabase db = _db.getReadableDatabase();

		String sql = "SELECT * FROM TBL_PROSPECT";
		
		Cursor cursor = db.rawQuery(sql , null);

		return cursor;
	}
	
	public Cursor getList(String aoCode, String filter, String sort) {
		SQLiteDatabase db = _db.getReadableDatabase();

		String sql = "SELECT 0 AS _id, PROSPECT_ID, FULL_NAME, ADDRESS_BUSINESS, INBOX, RENCANA_PLAFON "
				+ "FROM TBL_PROSPECT WHERE AO_CODE = '"+aoCode+"' AND IS_DELETED = '0' AND FULL_NAME LIKE '" + filter + "%'"
				+ (sort == null? "" : (" ORDER BY "+ sort));
		
		Cursor cursor = db.rawQuery(sql , null);

		return cursor;
	}
	
	public Cursor getList(String aoCode, String filter, boolean inbox, String sort) {
		SQLiteDatabase db = _db.getReadableDatabase();		

		Cursor cursor = db.rawQuery(
				"SELECT 0 AS _id, PROSPECT_ID, FULL_NAME, ADDRESS_BUSINESS, INBOX, RENCANA_PLAFON "
						+ "FROM TBL_PROSPECT WHERE AO_CODE = '"+aoCode+"' AND IS_DELETED = '0' AND INBOX = ? AND FULL_NAME LIKE '" + filter + "%'"
						+ (sort == null? "" : (" ORDER BY "+ sort)),
						new String[] { inbox? "1" : "0"});			
		
		return cursor;
	}	

	public Cursor getList(String aoCode, String sort) {
		SQLiteDatabase db = _db.getReadableDatabase();

		Cursor cursor = db
				.rawQuery("SELECT 0 AS _id, PROSPECT_ID, FULL_NAME, ADDRESS_BUSINESS, INBOX, RENCANA_PLAFON FROM TBL_PROSPECT WHERE AO_CODE='"+aoCode+"' AND IS_DELETED = '0'"
						+ sort == null? "" : (" ORDER BY "+ sort), null);

		return cursor;
	}

	public ProspectInfo get(String id) {
		SQLiteDatabase db = _db.getReadableDatabase();

		Cursor cursor = db.rawQuery(
				"SELECT * FROM TBL_PROSPECT WHERE PROSPECT_ID = ?",
				new String[] { id });

		if (cursor != null && cursor.getCount() > 0)
			cursor.moveToFirst();
		else {
			cursor.close();
			return null;
		}

		ProspectInfo pipeline = new ProspectInfo();	
		pipeline.setId(id);
		pipeline.setProspectNo(cursorGetString(cursor, "PROSPECT_NO"));
		pipeline.setFullName(cursorGetString(cursor, "FULL_NAME"));
		pipeline.setJenisUsaha(cursorGetString(cursor, "JENIS_USAHA"));
		pipeline.setRencanaPlafon(cursor.getLong(cursor
				.getColumnIndex("RENCANA_PLAFON")));
		pipeline.setMobilePhone(cursorGetString(cursor, "MOBILE_PHONE"));
		pipeline.setOfficePhone(cursorGetString(cursor, "OFFICE_PHONE"));
		pipeline.setResidentialPhone(cursorGetString(cursor, "RESIDENTIAL_PHONE"));
		pipeline.setKodyaKabBusiness(cursorGetString(cursor, "KODYA_KAB_BUSINESS"));
		pipeline.setKecamatanBusiness(cursorGetString(cursor, "KECAMATAN_BUSINESS"));
		pipeline.setKelurahanBusiness(cursorGetString(cursor, "KELURAHAN_BUSINESS"));
		pipeline.setProspectType(cursorGetString(cursor, "PROSPECT_TYPE"));
		pipeline.setDebtorType(cursorGetString(cursor, "DEBTOR_TYPE"));
		pipeline.setChanneling(cursorGetString(cursor, "CHANNELING"));
		pipeline.setZipCodeBusiness(cursorGetString(cursor, "ZIP_CODE_BUSINESS"));
		pipeline.setAddressBusiness(cursorGetString(cursor, "ADDRESS_BUSINESS"));
		pipeline.setEksternal(cursorGetString(cursor, "EKSTERNAL"));
		pipeline.setReferensi(cursorGetString(cursor, "REFERENSI"));
		pipeline.setEntryBy(cursorGetString(cursor, "ENTRY_BY"));
		pipeline.setEntryDate(cursorGetString(cursor, "ENTRY_DATE"));		
		pipeline.setUpdateBy(cursorGetString(cursor, "UPDATE_BY"));
		pipeline.setUpdateDate(cursorGetString(cursor, "UPDATE_DATE"));
		pipeline.setProgressStatus(cursorGetString(cursor, "PROGRESS_STATUS"));	
		pipeline.setFlagFreeze(cursor.getInt(cursor
				.getColumnIndex("FLAG_FREEZE")));

		cursor.close();

		return pipeline;
	}	
	
	public Cursor getListExport(String aoCode) {
		SQLiteDatabase db = _db.getReadableDatabase();

		Cursor cursor = db
				.rawQuery("SELECT * FROM TBL_PROSPECT WHERE AO_CODE = '"+aoCode+"' AND IS_UPLOAD = 0", null);

		return cursor;
	}
	
	public int updateProspectNotInterested(ProspectInfo info) {
		info.setIsUpload("0");		
		ContentValues values = new ContentValues();
		values.put("PROGRESS_STATUS", "RJD");
		values.put("NOT_INTERESTED_REASON", info.getNotInterestedReason());
		values.put("PROSPECT_FINISH_TIME", info.getProspectFinishTime());
		values.put("UPDATE_BY", info.getUpdateBy());
		values.put("UPDATE_DATE", info.getUpdateDate());
		values.put("IS_UPLOAD", info.getIsUpload());
		
		SQLiteDatabase db = _db.getWritableDatabase();				

		return db.update("TBL_PROSPECT", values, "PROSPECT_ID = ?",
				new String[] { info.getProspectID() });
	}
	
	public static void updateProspectFinishTime(SQLiteDatabase db, ProspectInfo info) throws ParseException {
		info.setIsUpload("0");
		ContentValues values = new ContentValues();
		values.put("PROGRESS_STATUS", info.getProgressStatus());				
		values.put("UPDATE_BY", info.getUpdateBy());
		values.put("UPDATE_DATE", info.getUpdateDate());
		values.put("IS_UPLOAD", "0");
		values.put("UM_DECISION_DATE", info.getProspectFinishTime());
		values.put("PROSPECT_FINISH_TIME", info.getProspectFinishTime());				

		db.update("TBL_PROSPECT", values, "PROSPECT_ID = ?",
				new String[] { info.getProspectID() });
		
		
		ProspectProgressInfo progress = new ProspectProgressInfo();
		progress.setProspectID(info.getProspectID());
		progress.setProgressStatus("RJS");
		progress.setEntryBy(info.getEntryBy());
		progress.setEntryDate(info.getEntryDate());
		progress.setUpdateBy(info.getUpdateBy());
		progress.setUpdateDate(info.getEntryDate());
		progress.setIsUpload("0");
		
		ProspectProgressDB.addOrUpdate(db, progress);
	}
	
	public int updateToPCD(ProspectInfo info) {
		info.setIsUpload("0");
		ContentValues values = new ContentValues();
		values.put("PROGRESS_STATUS", info.getProgressStatus());				
		values.put("UPDATE_BY", info.getUpdateBy());
		values.put("UPDATE_DATE", info.getUpdateDate());
		values.put("IS_UPLOAD", info.getIsUpload());
		values.put("IS_OFFLINE_ENTRY", "1");
		
		SQLiteDatabase db = _db.getWritableDatabase();	

		return db.update("TBL_PROSPECT", values, "PROSPECT_ID = ?",
				new String[] { info.getProspectID() });
	}
	
	public int updateProgress(ProspectInfo info) {
		info.setIsUpload("0");
		ContentValues values = new ContentValues();
		values.put("PROGRESS_STATUS", info.getProgressStatus());				
		values.put("UPDATE_BY", info.getUpdateBy());
		values.put("UPDATE_DATE", info.getUpdateDate());
		values.put("IS_UPLOAD", info.getIsUpload());
		
		SQLiteDatabase db = _db.getWritableDatabase();	

		return db.update("TBL_PROSPECT", values, "PROSPECT_ID = ?",
				new String[] { info.getProspectID() });
	}
	
	public int updateProspectStartTimeAndPipelineFinishTime(ProspectInfo info) {
		info.setIsUpload("0");
		ContentValues values = new ContentValues();
		values.put("PIPELINE_FINISH_TIME", info.getPipelineFinishTime());
		values.put("PROSPECT_START_TIME", info.getProspectStartTime());
		values.put("UPDATE_BY", info.getUpdateBy());
		values.put("UPDATE_DATE", info.getUpdateDate());
		values.put("IS_UPLOAD", info.getIsUpload());
				
		
		SQLiteDatabase db = _db.getWritableDatabase();	

		return db.update("TBL_PROSPECT", values, "PROSPECT_ID = ?",
				new String[] { info.getProspectID() });
	}
	
	public int updateProspectFinishTime(ProspectInfo info) {
		info.setIsUpload("0");
		ContentValues values = new ContentValues();
		values.put("PROSPECT_FINISH_TIME", info.getProspectFinishTime());
		values.put("UPDATE_BY", info.getUpdateBy());
		values.put("UPDATE_DATE", info.getUpdateDate());
		values.put("IS_UPLOAD", info.getIsUpload());
		
		SQLiteDatabase db = _db.getWritableDatabase();	

		return db.update("TBL_PROSPECT", values, "PROSPECT_ID = ?",
				new String[] { info.getProspectID() });
	}
	
	public int updateProspectKeputusanUM(ProspectInfo info) {
		info.setIsUpload("0");
		ContentValues values = new ContentValues();
		values.put("PROGRESS_STATUS", "RJU");
		values.put("REPUTASI_DEBITUR", "SPADA");
		values.put("UM_DECISION", info.getUmDecision());
		values.put("UM_DECISION_NOTE", info.getUmDecisionNote());
		values.put("UM_DECISION_DATE", info.getUmDecisionDate());
		values.put("CO_ASSIGNED", info.getCoAssigned());
		values.put("ALTERNATE_FOR", info.getAlternateFor());
		values.put("PROSPECT_FINISH_TIME", info.getProspectFinishTime());
		values.put("UPDATE_BY", info.getUpdateBy());
		values.put("UPDATE_DATE", info.getUpdateDate());
		values.put("IS_UPLOAD", info.getIsUpload());
		
		SQLiteDatabase db = _db.getWritableDatabase();	

		return db.update("TBL_PROSPECT", values, "PROSPECT_ID = ?",
				new String[] { info.getProspectID() });
	}
	
	
	
	public long add(ProspectInfo info) {
		ContentValues values = createContentValues(info);
		values.put("INBOX", 0);				
		values.put("ENTRY_BY", info.getEntryBy());
		values.put("ENTRY_DATE", info.getEntryDate());
		values.put("PIPELINE_START_TIME", info.getPipelineStartTime());
		values.put("PROGRESS_STATUS", "NEW");
		values.put("IS_DELETED", "0");
		values.put("IS_OFFLINE_ENTRY", "1");
		values.put("IS_PIPELINE_KP", "0");
		values.put("SOURCE_CODE", info.getSourceCode());
		values.put("FLAG_FREEZE", "0");
		values.put("NIP", info.getEntryBy());
		values.put("KARAKTER", "5");
		
		SQLiteDatabase db = _db.getWritableDatabase();

		return db.insert("TBL_PROSPECT", null, values);
	}	

	public int update(ProspectInfo info) {
		ContentValues values = createContentValues(info);
		values.put("INBOX", 0);

		SQLiteDatabase db = _db.getWritableDatabase();

		return db.update("TBL_PROSPECT", values, "PROSPECT_ID = ?",
				new String[] { info.getProspectID() });
	}
	
	private static ContentValues createContentValues(ProspectInfo info) {
		ContentValues values = new ContentValues();
		values.put("PROSPECT_ID", info.getProspectID());
		values.put("FULL_NAME", info.getFullName());
		values.put("JENIS_USAHA", info.getJenisUsaha());
		values.put("RENCANA_PLAFON", info.getRencanaPlafon());
		values.put("MOBILE_PHONE", info.getMobilePhone());
		values.put("OFFICE_PHONE", info.getOfficePhone());
		values.put("RESIDENTIAL_PHONE", info.getResidentialPhone());
		values.put("KODYA_KAB_BUSINESS", info.getKodyaKabBusiness());
		values.put("KECAMATAN_BUSINESS", info.getKecamatanBusiness());
		values.put("KELURAHAN_BUSINESS", info.getKelurahanBusiness());
		values.put("PROSPECT_TYPE", info.getProspectType());
		values.put("DEBTOR_TYPE", info.getDebtorType());
		values.put("CHANNELING", info.getChanneling());
		values.put("ZIP_CODE_BUSINESS", info.getZipCodeBusiness());
		values.put("ADDRESS_BUSINESS", info.getAddressBusiness());
		values.put("EKSTERNAL", info.getEksternal());
		values.put("REFERENSI", info.getReferensi());		
		values.put("PROSPECT_NO", info.getProspectNo());
		//values.put("INBOX", info.isInbox());
		values.put("UPDATE_BY", info.getUpdateBy());
		values.put("UPDATE_DATE", info.getUpdateDate());		
		values.put("IS_UPLOAD", info.getIsUpload());
		values.put("AO_CODE", info.getAoCode());
		values.put("UNIT_CODE", info.getUnitCode());	
		values.put("GU_FUP", info.getGuFup());		
		return values;
	}
	
	/*
	public void openInbox(String id) {
		ContentValues values = new ContentValues();
		values.put("INBOX", 1);

		SQLiteDatabase db = _db.getWritableDatabase();

		db.update("TBL_PIPELINE", values, "PROSPECT_ID = ?",
				new String[] { id });
	}
	
	public void makeAllInbox() {		
		SQLiteDatabase db = _db.getWritableDatabase();

		db.execSQL("UPDATE TBL_PROSPECT SET INBOX = 1");		
	}	
	
	public boolean isProspect(String id) {
		
		SQLiteDatabase db = _db.getReadableDatabase();

		Cursor cursor = db
				.rawQuery(
						"SELECT PROSPECT_ID FROM TBL_PROSPECT WHERE PROSPECT_ID = ? AND STATUS = ?",
						new String[] { String.valueOf(id),
								String.valueOf(PROSPECT_STATUS_PROSPECT) });

		boolean result = cursor.getCount() > 0;
		cursor.close();
		
		return false;
	}*/
	
	public Cursor getList(String aoCode) {
		SQLiteDatabase db = _db.getReadableDatabase();

		Cursor cursor = db.rawQuery("SELECT * FROM TBL_PROSPECT WHERE AO_CODE = '"+aoCode+"'", null);

		return cursor;
	}

	public Cursor getList() {
		SQLiteDatabase db = _db.getReadableDatabase();

		Cursor cursor = db.rawQuery("SELECT * FROM TBL_PROSPECT", null);

		return cursor;
	}
	
	/*
	public void rejectProspectBySystem(String date) {
		
		SQLiteDatabase db = _db.getReadableDatabase();	
		
		Cursor cursor = db
				.rawQuery("SELECT PROSPECT_ID FROM TBL_PROSPECT WHERE (PROGRESS_STATUS = 'NEW' OR PROGRESS_STATUS = 'FLU') AND ",
						new String[] { id });
		
		List<String> list = new ArrayList<String>();

		if (cursor.moveToFirst()) {
			do {
				OptionInfo info = new OptionInfo(cursor.getString(0),
						cursor.getString(1));
				list.add(info);
			} while (cursor.moveToNext());
		}

		cursor.close();
	}*/
	
	public int updateGuCloseDeal(String id, String date) {
		SQLiteDatabase db = _db.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("GU_CLOSE_DEAL", date);
		return db.update("TBL_PROSPECT", values, "PROSPECT_ID = ?",
				new String[] { id });
	}

	public ProspectInfo getDetails(String id) {
		SQLiteDatabase db = _db.getReadableDatabase();

		Cursor cursor = db
				.rawQuery(
						"SELECT AVG_EXPENSE, DATE_OF_BIRTH, MARITAL_STATUS,"
								+ "SPOUSE_NAME, JML_TANGGUNGAN, PRODUCT_CODE, KTP_NO, "
								+ "KTP_NAME, KTP_ADDRESS, ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE, IS_UPLOAD, PLACE_OF_BIRTH FROM TBL_PROSPECT WHERE PROSPECT_ID = ?",
						new String[] { id });

		if (cursor != null && cursor.getCount() > 0)
			cursor.moveToFirst();
		else {
			cursor.close();
			return null;
		}

		ProspectInfo info = new ProspectInfo();
		info.setId(id);
		info.setAvgExpense(cursor.getLong(cursor.getColumnIndex("AVG_EXPENSE")));
		info.setDateOfBirth(cursorGetString(cursor, "DATE_OF_BIRTH"));
		info.setMaritalStatus(cursorGetString(cursor, "MARITAL_STATUS"));
		info.setSpouseName(cursorGetString(cursor, "SPOUSE_NAME"));
		info.setJmlTanggungan(cursor.getInt(cursor
				.getColumnIndex("JML_TANGGUNGAN")));
		info.setProductCode(cursorGetString(cursor, "PRODUCT_CODE"));
		info.setKtpNo(cursorGetString(cursor, "KTP_NO"));
		info.setKtpName(cursorGetString(cursor, "KTP_NAME"));
		info.setKtpAddress(cursorGetString(cursor, "KTP_ADDRESS"));
		info.setEntryBy(cursorGetString(cursor, "ENTRY_BY"));
		info.setEntryDate(cursorGetString(cursor, "ENTRY_DATE"));
		info.setUpdateBy(cursorGetString(cursor, "UPDATE_BY"));
		info.setUpdateDate(cursorGetString(cursor, "UPDATE_DATE"));
		info.setIsUpload(cursorGetString(cursor, "IS_UPLOAD"));
		info.setPlaceOfBirth(cursorGetString(cursor, "PLACE_OF_BIRTH"));

		cursor.close();

		return info;
	}
	
	public boolean hasPrn(String id) {
		
		SQLiteDatabase db = _db.getReadableDatabase();

		Cursor cursor = db
				.rawQuery(
						"SELECT PROSPECT_NO FROM TBL_PROSPECT WHERE PROSPECT_ID = ?",
						new String[] { String.valueOf(id)});
		
		if (cursor != null && cursor.getCount() > 0) {
			cursor.moveToFirst();
			String no = cursor.getString(cursor.getColumnIndex("PROSPECT_NO"));
			boolean result = !Str.isNullOrEmpty(no);
			cursor.close();
			
			return result;
		} else {
			cursor.close();
			return false;
		}					
	}
	
	public boolean isClosed(String id) {
		
		SQLiteDatabase db = _db.getReadableDatabase();

		Cursor cursor = db
				.rawQuery(
						"SELECT PROGRESS_STATUS FROM TBL_PROSPECT WHERE PROSPECT_ID = ?",
						new String[] { String.valueOf(id)});
		
		if (cursor != null && cursor.getCount() > 0) {
			cursor.moveToFirst();
			String no = cursor.getString(cursor.getColumnIndex("PROGRESS_STATUS"));
			boolean result = "CD".equalsIgnoreCase(no) || "RJD".equalsIgnoreCase(no) || "RJU".equalsIgnoreCase(no) || "RJS".equalsIgnoreCase(no);
			
			cursor.close();
			
			return result;
		} else {
			cursor.close();
			return false;
		}					
	}

	public ProspectInfo getFullInfo(String id) {
		SQLiteDatabase db = _db.getReadableDatabase();

		Cursor cursor = db
				.rawQuery(
						"SELECT P.PROSPECT_ID, P.FULL_NAME, JP.STD_TEXT JENIS_USAHA, P.RENCANA_PLAFON, "
								+ "P.MOBILE_PHONE, P.OFFICE_PHONE, P.RESIDENTIAL_PHONE, P.KODYA_KAB_BUSINESS, P.KECAMATAN_BUSINESS, P.KELURAHAN_BUSINESS, "
								+ "PT.STD_TEXT PROSPECT_TYPE, DT.STD_TEXT DEBTOR_TYPE, CH.STD_TEXT CHANNELING, "
								+ "P.ZIP_CODE_BUSINESS, P.ADDRESS_BUSINESS, ET.STD_TEXT EKSTERNAL, P.REFERENSI, P.PROSPECT_NO, PS.STD_TEXT AS PROGRESS_STATUS, P.AVG_EXPENSE, "
								+ "P.DATE_OF_BIRTH, MS.STD_TEXT MARITAL_STATUS, P.SPOUSE_NAME, P.JML_TANGGUNGAN,PD.STD_TEXT PRODUCT_CODE, "
								+ "P.KTP_NO, P.KTP_NAME, P.KTP_ADDRESS, P.ENTRY_DATE, P.UPDATE_DATE, P.IS_UPLOAD,P.INBOX, "
								+ "P.UPLOAD_BY,P.UPLOAD_DATE, P.AO_CODE, P.UNIT_CODE, P.LOAN_NUMBER, P.CIF_NUMBER, "
								+ "P.OMZET_1, P.OMZET_2, P.OMZET_3, P.OMZET_4, P.OMZET_5, P.OMZET_6, P.OMZET_7," 
								+ "P.OMZET_8, P.OMZET_9, P.OMZET_10, P.OMZET_11, P.OMZET_12, P.OMZET_AVERAGE,P.SOURCE_CODE, P.PIPELINE_START_TIME, P.PIPELINE_FINISH_TIME, P.PROSPECT_START_TIME, P.PROSPECT_FINISH_TIME, P.GU_CLOSE_DEAL, P.GU_FUP, P.IS_PIPELINE_KP,P.FLAG_FREEZE,P.PLACE_OF_BIRTH,NIP "
								+ "FROM TBL_PROSPECT P "
								+ "LEFT JOIN TBL_MS_STANDARD JP ON JP.STD_GROUP = '"+ StandardDB.GROUP_JOB +"' AND JP.STD_VALUE = P.JENIS_USAHA "
								+ "LEFT JOIN TBL_MS_STANDARD PT ON PT.STD_GROUP = '"+ StandardDB.GROUP_PROSPECT_TYPE +"' AND PT.STD_VALUE = P.PROSPECT_TYPE "
								+ "LEFT JOIN TBL_MS_STANDARD DT ON DT.STD_GROUP = '"+ StandardDB.GROUP_DEBTOR_TYPE +"' AND DT.STD_VALUE = P.DEBTOR_TYPE "
								+ "LEFT JOIN TBL_MS_STANDARD CH ON CH.STD_GROUP = '"+ StandardDB.GROUP_CHANNELING2 +"' AND CH.STD_VALUE = P.CHANNELING "
								+ "LEFT JOIN TBL_MS_STANDARD ET ON ET.STD_GROUP = '"+ StandardDB.GROUP_EXTERNAL_TYPE +"' AND ET.STD_VALUE = P.EKSTERNAL "
								+ "LEFT JOIN TBL_MS_STANDARD MS ON MS.STD_GROUP = '"+ StandardDB.GROUP_MARITAL_STATUS +"' AND MS.STD_VALUE = P.MARITAL_STATUS "
								+ "LEFT JOIN TBL_MS_STANDARD PS ON PS.STD_GROUP = '"+ StandardDB.GROUP_PROGRESS_STATUS +"' AND PS.STD_VALUE = P.PROGRESS_STATUS "
								+ "LEFT JOIN TBL_MS_STANDARD PD ON PD.STD_GROUP = '"+ StandardDB.GROUP_PRODUCT +"' AND PD.STD_VALUE = P.PRODUCT_CODE WHERE P.PROSPECT_ID = ? ",
						new String[] { id });

		if (cursor != null && cursor.getCount() > 0)
			cursor.moveToFirst();
		else {
			cursor.close();
			return null;
		}

		ProspectInfo info = new ProspectInfo();
		info.setId(id);
		info.setProspectNo(cursorGetString(cursor, "PROSPECT_NO"));
		info.setFullName(cursorGetString(cursor, "FULL_NAME"));
		info.setJenisUsaha(cursorGetString(cursor, "JENIS_USAHA"));
		info.setRencanaPlafon(cursor.getLong(cursor
				.getColumnIndex("RENCANA_PLAFON")));
		info.setMobilePhone(cursorGetString(cursor, "MOBILE_PHONE"));
		info.setOfficePhone(cursorGetString(cursor, "OFFICE_PHONE"));
		info.setResidentialPhone(cursorGetString(cursor, "RESIDENTIAL_PHONE"));
		info.setKodyaKabBusiness(cursorGetString(cursor, "KODYA_KAB_BUSINESS"));
		info.setKecamatanBusiness(cursorGetString(cursor, "KECAMATAN_BUSINESS"));
		info.setKelurahanBusiness(cursorGetString(cursor, "KELURAHAN_BUSINESS"));
		info.setProspectType(cursorGetString(cursor, "PROSPECT_TYPE"));
		info.setDebtorType(cursorGetString(cursor, "DEBTOR_TYPE"));
		info.setChanneling(cursorGetString(cursor, "CHANNELING"));
		info.setZipCodeBusiness(cursorGetString(cursor, "ZIP_CODE_BUSINESS"));
		info.setAddressBusiness(cursorGetString(cursor, "ADDRESS_BUSINESS"));
		info.setEksternal(cursorGetString(cursor, "EKSTERNAL"));
		info.setReferensi(cursorGetString(cursor, "REFERENSI"));		
		
		info.setProgressStatus(cursorGetString(cursor, "PROGRESS_STATUS"));
		info.setAvgExpense(cursor.getLong(cursor.getColumnIndex("AVG_EXPENSE")));
		info.setDateOfBirth(cursorGetString(cursor, "DATE_OF_BIRTH"));
		info.setMaritalStatus(cursorGetString(cursor, "MARITAL_STATUS"));
		info.setSpouseName(cursorGetString(cursor, "SPOUSE_NAME"));
		info.setJmlTanggungan(cursor.getInt(cursor
				.getColumnIndex("JML_TANGGUNGAN")));
		info.setProductCode(cursorGetString(cursor, "PRODUCT_CODE"));
		info.setKtpNo(cursorGetString(cursor, "KTP_NO"));
		info.setKtpName(cursorGetString(cursor, "KTP_NAME"));
		info.setKtpAddress(cursorGetString(cursor, "KTP_ADDRESS"));
		
		info.setEntryDate(cursorGetString(cursor, "ENTRY_DATE"));
		info.setUpdateDate(cursorGetString(cursor, "UPDATE_DATE"));		
		info.setIsUpload(cursorGetString(cursor, "IS_UPLOAD"));
		info.setUploadBy(cursorGetString(cursor, "UPLOAD_BY"));
		info.setUploadDate(cursorGetString(cursor, "UPLOAD_DATE"));
		info.setAoCode(cursorGetString(cursor, "AO_CODE"));
		info.setUnitCode(cursorGetString(cursor, "UNIT_CODE"));
		
		info.setOmzet1(cursor.getLong(cursor.getColumnIndex("OMZET_1")));
		info.setOmzet2(cursor.getLong(cursor.getColumnIndex("OMZET_2")));
		info.setOmzet3(cursor.getLong(cursor.getColumnIndex("OMZET_3")));
		info.setOmzet4(cursor.getLong(cursor.getColumnIndex("OMZET_4")));
		info.setOmzet5(cursor.getLong(cursor.getColumnIndex("OMZET_5")));
		info.setOmzet6(cursor.getLong(cursor.getColumnIndex("OMZET_6")));
		info.setOmzet7(cursor.getLong(cursor.getColumnIndex("OMZET_7")));
		info.setOmzet8(cursor.getLong(cursor.getColumnIndex("OMZET_8")));
		info.setOmzet9(cursor.getLong(cursor.getColumnIndex("OMZET_9")));
		info.setOmzet10(cursor.getLong(cursor.getColumnIndex("OMZET_10")));
		info.setOmzet11(cursor.getLong(cursor.getColumnIndex("OMZET_11")));
		info.setOmzet12(cursor.getLong(cursor.getColumnIndex("OMZET_12")));
		info.setOmzetAverage(cursor.getInt(cursor.getColumnIndex("OMZET_AVERAGE")));
		
		info.setLoanNumber(cursorGetString(cursor, "LOAN_NUMBER"));
		info.setCIFNumber(cursor.getInt(cursor.getColumnIndex("CIF_NUMBER")));			
		
		info.setSourceCode(cursor.getString(cursor.getColumnIndex("SOURCE_CODE")));
		
		info.setPipelineStartTime(cursor.getString(cursor.getColumnIndex("PIPELINE_START_TIME")));
		info.setPipelineFinishTime(cursor.getString(cursor.getColumnIndex("PIPELINE_FINISH_TIME")));
		info.setProspectStartTime(cursor.getString(cursor.getColumnIndex("PROSPECT_START_TIME")));
		info.setProspectFinishTime(cursor.getString(cursor.getColumnIndex("PROSPECT_FINISH_TIME")));
		
		info.setIsPipelineKP(cursor.getString(cursor.getColumnIndex("IS_PIPELINE_KP")));
		info.setGuCloseDeal(cursor.getString(cursor.getColumnIndex("GU_CLOSE_DEAL")));
		info.setGuFup(cursor.getString(cursor.getColumnIndex("GU_FUP")));
		info.setInbox(cursor.getInt(cursor.getColumnIndex("INBOX")) == 1);
		info.setNIP(cursor.getString(cursor.getColumnIndex("NIP")));
		
		info.setFlagFreeze(cursor.getInt(cursor
				.getColumnIndex("FLAG_FREEZE")));
		
		info.setPlaceOfBirth(cursor.getString(cursor.getColumnIndex("PLACE_OF_BIRTH")));

		cursor.close();

		return info;
	}	
	
	public static void addOrUpdate(SQLiteDatabase db, ProspectInfo info, boolean inbox) throws ParseException {
		ContentValues values = createContentValues(info);
		values.putAll(createContentValuesDetails(info));		
		//values.put("INBOX", 1);		
		//values.put("DOWNLOAD_DATE", getCurrentDateTime());
		values.put("IS_DELETED", info.getIsDeleted());
		values.put("PROGRESS_STATUS", info.getProgressStatus());
		values.put("ENTRY_BY", info.getEntryBy());
		values.put("ENTRY_DATE", info.getEntryDate());
		values.put("IS_OFFLINE_ENTRY", info.getIsOfflineEntry());
		values.put("OMZET_1", info.getOmzet1());
		values.put("OMZET_2", info.getOmzet2());
		values.put("OMZET_3", info.getOmzet3());
		values.put("OMZET_4", info.getOmzet4());
		values.put("OMZET_5", info.getOmzet5());
		values.put("OMZET_6", info.getOmzet6());
		values.put("OMZET_7", info.getOmzet7());
		values.put("OMZET_8", info.getOmzet8());
		values.put("OMZET_9", info.getOmzet9());
		values.put("OMZET_10", info.getOmzet10());
		values.put("OMZET_11", info.getOmzet11());
		values.put("OMZET_12", info.getOmzet12());
		values.put("OMZET_AVERAGE", info.getOmzetAverage());
		values.put("SOURCE_CODE", info.getSourceCode());
		values.put("PIPELINE_START_TIME", info.getPipelineStartTime());
		values.put("PIPELINE_FINISH_TIME", info.getPipelineFinishTime());
		values.put("PROSPECT_START_TIME", info.getProspectStartTime());
		values.put("PROSPECT_FINISH_TIME", info.getProspectFinishTime());
		values.put("IS_PIPELINE_KP", info.getIsPipelineKP());
		values.put("GU_CLOSE_DEAL", info.getGuCloseDeal());		
		values.put("GU_FUP", info.getGuFup());			
		values.put("LOAN_NUMBER", info.getLoanNumber());
		values.put("CIF_NUMBER", info.getCIFNumber());
		values.put("UPLOAD_BY", info.getUploadBy());
		values.put("UPLOAD_DATE", info.getUploadDate());
		values.put("FLAG_FREEZE", info.getFlagFreeze());
		values.put("NIP", info.getNIP());
					
		Cursor cursor = db.rawQuery("SELECT UPDATE_DATE FROM TBL_PROSPECT WHERE PROSPECT_ID=?", new String[] {info.getProspectID()});
						
		Date upd = null;
		if (cursor != null && cursor.getCount() > 0) {
			cursor.moveToFirst();
			upd = cursorGetDate(cursor, "UPDATE_DATE");			
		}
		cursor.close();									
		
		if (upd == null) {						
			if (inbox)
				values.put("INBOX", 1);
			db.insert("TBL_PROSPECT", null, values);
		} else if ( dateFormat.parse(info.getUpdateDate()).getTime() > upd.getTime()) {			
			db.update("TBL_PROSPECT", values, "PROSPECT_ID = ?",
					new String[] { info.getProspectID()});
		}
	}	

	public void updateDetails(ProspectInfo info) {
		ContentValues values = createContentValuesDetails(info);
		
		SQLiteDatabase db = _db.getWritableDatabase();

		db.update("TBL_PROSPECT", values, "PROSPECT_ID = ?",
				new String[] { info.getProspectID() });
	}	
	
	public static int updateUpload(SQLiteDatabase db, String id, String uploadBy) {
		ContentValues values = new ContentValues();
		values.put("IS_UPLOAD", 1);
		values.put("UPLOAD_DATE", getCurrentDateTime());
		values.put("UPLOAD_BY", uploadBy);
			
		return db.update("TBL_PROSPECT", values, "PROSPECT_ID = ?",
				new String[] { id });
	}

	private static ContentValues createContentValuesDetails(ProspectInfo info) {
		ContentValues values = new ContentValues();
		values.put("AVG_EXPENSE", info.getAvgExpense());
		values.put("DATE_OF_BIRTH", info.getDateOfBirth());
		values.put("MARITAL_STATUS", info.getMaritalStatus());
		values.put("SPOUSE_NAME", info.getSpouseName());
		values.put("JML_TANGGUNGAN", info.getJmlTanggungan());
		values.put("PRODUCT_CODE", info.getProductCode());
		values.put("KTP_NO", info.getKtpNo());
		values.put("KTP_NAME", info.getKtpName());
		values.put("KTP_ADDRESS", info.getKtpAddress());			
		values.put("UPDATE_BY", info.getUpdateBy());
		values.put("UPDATE_DATE", info.getUpdateDate());
		values.put("IS_UPLOAD", info.getIsUpload());
		values.put("PLACE_OF_BIRTH", info.getPlaceOfBirth());
		return values;
	}

	public void delete(String id, String updateBy) {
		SQLiteDatabase db = _db.getWritableDatabase();

		db.beginTransaction();
		try {
			ContentValues values = new ContentValues();
			values.put("IS_DELETED", "1");
			values.put("IS_UPLOAD", "0");
			values.put("UPDATE_DATE", getCurrentDateTime());
			values.put("UPDATE_BY", updateBy);
			
			
			db.update("TBL_PROSPECT_VISIT", values, "PROSPECT_ID = ?",
					new String[] { id });
			db.update("TBL_PROSPECT", values, "PROSPECT_ID = ?",
					new String[] { id });
			
			db.setTransactionSuccessful();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.endTransaction();
		}
	}
	
	public void rejectProspectBySystem(String updateBy, String date)
    {
		SQLiteDatabase rDB = _db.getReadableDatabase();
		SQLiteDatabase wDB = _db.getWritableDatabase();
		
        String sql = "SELECT PROSPECT_ID FROM TBL_PROSPECT WHERE ((PROGRESS_STATUS = 'NEW' AND GU_FUP > '2009-01-01' AND GU_FUP <= ?) OR (RTRIM(LTRIM(PROGRESS_STATUS)) IN ('FLU','PCD') AND GU_CLOSE_DEAL > '2009-01-01' AND GU_CLOSE_DEAL <= ?)) AND IS_DELETED = '0'";
        Cursor cursor = rDB.rawQuery(sql, new String[] { date, date });
                        
        if (cursor.moveToFirst()) {
			do {
				String id = cursor.getString(0);
				
				ContentValues values = new ContentValues();
				values.put("PROGRESS_STATUS", "RJS");
				values.put("UPDATE_BY", updateBy);
				values.put("UPDATE_DATE", date);
				values.put("UM_DECISION_DATE", date);
				values.put("PROSPECT_FINISH_TIME", date);
				values.put("IS_UPLOAD", "0");
				
				wDB.update("TBL_PROSPECT", values, "PROSPECT_ID = ?", new String[] { id });
				
			} while (cursor.moveToNext());
		}
        
        cursor.close();                      
    }	
	
	public static void addOrUpdateProspectRefferal(SQLiteDatabase db, ProspectInfo info) throws ParseException {
						
		ContentValues values = new ContentValues();
		values.put("PROSPECT_ID", info.getProspectID());
		values.put("PROSPECT_NO", info.getProspectNo());
		values.put("UNIT_CODE", info.getUnitCode());
		values.put("SOURCE_CODE", info.getSourceCode());
		values.put("AO_CODE", info.getAoCode());
		values.put("FULL_NAME", info.getFullName());
		values.put("JENIS_USAHA", info.getJenisUsaha());
		values.put("RENCANA_PLAFON", info.getRencanaPlafon());
		values.put("OFFICE_PHONE", info.getOfficePhone());
		values.put("ADDRESS_BUSINESS", info.getAddressBusiness());
		values.put("PROGRESS_STATUS", info.getProgressStatus());
		values.put("ENTRY_BY", info.getEntryBy());
		values.put("ENTRY_DATE", info.getEntryDate());
		values.put("UPDATE_BY", info.getEntryBy());
		values.put("UPDATE_DATE", info.getEntryDate());
		values.put("PIPELINE_START_TIME", info.getEntryDate());
		values.put("GU_FUP", info.getGuFup());		
		values.put("IS_UPLOAD", "0");		
		values.put("KARAKTER", "4");		
		
		values.put("IS_DELETED", "0");
		values.put("INBOX", 1);
		values.put("NIP", info.getEntryBy());
		
		Cursor cursor = db.rawQuery("SELECT UPDATE_DATE FROM TBL_PROSPECT WHERE PROSPECT_ID=?", new String[] {info.getProspectID()});
						
		Date upd = null;
		if (cursor != null && cursor.getCount() > 0) {
			cursor.moveToFirst();
			upd = cursorGetDate(cursor, "UPDATE_DATE");			
		}
		cursor.close();									
		
		if (upd == null) {						
			db.insert("TBL_PROSPECT", null, values);
		} else if ( dateFormat.parse(info.getEntryDate()).getTime() > upd.getTime()) {			
			db.update("TBL_PROSPECT", values, "PROSPECT_ID = ?",
					new String[] { info.getProspectID()});
		}
	}
}
